package com.bmadmin.service.impl;

import com.bmadmin.domain.DataAutoRunPOJO;
import com.bmadmin.domain.DataQuickQueryPOJO;
import com.bmadmin.domain.DataSourcePOJO;
import com.bmadmin.domain.NormalResult;
import com.bmadmin.domain.SysUsersPOJO;
import com.bmadmin.mapper.DataSourcePOJODao;
import com.bmadmin.service.DataQuickQueryService;
import com.bmadmin.util.BMUtil;
import com.bmadmin.util.DataSource;
import com.bmadmin.util.Util;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import net.sf.json.JSONObject;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

import static com.bmadmin.util.Util.resultSetToJson;

/**
 * Created by SEELE on 2017/12/17.
 */
@Service
@Transactional
public class DataQuickQueryServiceImpl implements DataQuickQueryService {
	private Logger logger = LogManager.getLogger(this.getClass().getName());
    @Autowired
    DataSourcePOJODao dataSourcePOJODao;

    @Override
    public NormalResult getQueryResult(DataQuickQueryPOJO dataQuickQueryPOJO) {
        NormalResult result = new NormalResult();

        DataSourcePOJO dataSourcePOJOForQuery = new DataSourcePOJO();
        dataSourcePOJOForQuery.setT_id(dataQuickQueryPOJO.getDataSourceId());

        DataSourcePOJO dataSourcePOJOS = dataSourcePOJODao.find(dataSourcePOJOForQuery).get(0);

        Connection conn =DataSource.getConnOra(dataSourcePOJOS.getT_driver(),dataSourcePOJOS.getT_url(),dataSourcePOJOS.getT_user(),dataSourcePOJOS.getT_password());

        Statement smt = null;
        try{


			 smt = conn.createStatement();
//            System.out.println(dataQuickQueryPOJO.getSql());
            ResultSet sqlQueryResult =  smt.executeQuery(dataQuickQueryPOJO.getSql());
			String list = resultSetToJson(sqlQueryResult);
//            System.out.println(list);
			result.setCode("0000");
			result.setMsg("查询成功");
			result.setData(list);

            if(list.equals("[]")){
                return result;
            }

			//导出Excel
            String s = BMUtil.NulltoZero(dataQuickQueryPOJO.getIsExportExcel());
            if(!s.equals("0")){
                ObjectMapper mapper = new ObjectMapper();
                List<Map> listMap= mapper.readValue(list,new TypeReference<List<Map>>() {});

                // 声明一个工作薄
                HSSFWorkbook workbook = new HSSFWorkbook();
                // 用于格式化单元格的数据
                HSSFDataFormat format = workbook.createDataFormat();

                HSSFCellStyle stylecol = workbook.createCellStyle();
                stylecol.setDataFormat(format.getFormat("@"));
                // 设置单元格类型
                HSSFCellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平布局：居中
                cellStyle.setWrapText(true);

                HSSFSheet sheet = workbook.createSheet();

                //设置导出列宽
                //sheet.setColumnWidth(0,20*240);

                // 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
                HSSFRow row = sheet.createRow((int) 0);
                sheet.autoSizeColumn(1, true);

                HSSFFont font = workbook.createFont();
                /*
                 * font.setFontName("黑体"); font.setFontHeightInPoints((short)
                 * 16);//设置字体大小 HSSFFont font2 = workbook.createFont();
                 * font2.setFontName("仿宋_GB2312");
                 * font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
                 * font2.setFontHeightInPoints((short) 12);
                 */
                font.setColor(HSSFColor.RED.index);

                // 创建单元格，并设置值表头 设置表头居中
                HSSFCellStyle style = workbook.createCellStyle();
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

                HSSFCell cell;

                Set<String> keySet =listMap.get(0).keySet();
                Iterator<String> keyIterator = keySet.iterator();
                int i=0;
                while (keyIterator.hasNext() ){
                    cell = row.createCell(i);
                    cell.setCellValue(keyIterator.next());
                    cell.setCellStyle(style);
                    i++;
                }


                int line=0;
                for(Map listItem:listMap){
                    row = sheet.createRow((int) line + 1);

                    Set<String> itemkeySet = listItem.keySet();
                    Collection<String> itemvalueSet =listItem.values();

                    Iterator<String> keyIteratorItem = itemkeySet.iterator();
                    Iterator<String> valueIteatorItem = itemvalueSet.iterator();
                    int j=0;
                    while (keyIteratorItem.hasNext() ){
                        keyIteratorItem.next();
                        String lineValue=valueIteatorItem.next();

                        cell = row.createCell(j);
                        cell.setCellValue(lineValue);
                        cell.setCellStyle(style);
                        sheet.setDefaultColumnStyle(j, stylecol);
                        j++;
                    }

                    line++;
                }

                FileOutputStream fos = null;
                String uuid = BMUtil.getUUID();
                try {

//                    String filename = "/Users/bomber/items/own/webstorm/hzwgs/exportfile/"+uuid+".xls";
                    String filename = "D:\\soft\\nginx\\html\\hzwgs\\exportfile\\"+uuid+".xls";

                    fos = new FileOutputStream(filename);

                    workbook.write(fos);
                    fos.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                smt.close();
                conn.close();
                result.setObject(uuid);
            }


        }catch (Exception e) {
			e.printStackTrace();
			result.setMsg("查询异常,请检查sql 语句");
			result.setCode("9999");
		}finally {
            try {
                smt.close();
            } catch (SQLException e) {
            }
            try {
                conn.close();
            } catch (SQLException e) {
            }

        }

        return result;
    }

    @Override
    public int exportExcel(DataQuickQueryPOJO dataQuickQueryPOJO) {
        int result = 0;   //0  代表不成功，1代表成功

        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 用于格式化单元格的数据
        HSSFDataFormat format = workbook.createDataFormat();

        HSSFCellStyle stylecol = workbook.createCellStyle();
        stylecol.setDataFormat(format.getFormat("@"));
        // 设置单元格类型
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平布局：居中
        cellStyle.setWrapText(true);

        HSSFSheet sheet = workbook.createSheet();

        //设置导出列宽
//        sheet.setColumnWidth(0,20*240);

        // 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        sheet.autoSizeColumn(1, true);

        HSSFFont font = workbook.createFont();
        /*
         * font.setFontName("黑体"); font.setFontHeightInPoints((short)
         * 16);//设置字体大小 HSSFFont font2 = workbook.createFont();
         * font2.setFontName("仿宋_GB2312");
         * font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
         * font2.setFontHeightInPoints((short) 12);
         */
        font.setColor(HSSFColor.RED.index);

        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        NormalResult queryResult = this.getQueryResult(dataQuickQueryPOJO);



        return result;
    }
    
    @Override
    public NormalResult SaveAsyncQuery(DataQuickQueryPOJO dataQuickQueryPOJO,
			SysUsersPOJO user){
    	NormalResult result = new NormalResult();
    	try{
        	DataAutoRunPOJO DataAutoRun = new DataAutoRunPOJO();
        	if("".equals(Util.NulltoString(String.valueOf(dataQuickQueryPOJO.getDataSourceId())))){
        		logger.info("========参数出错:数据源主键不可为空========");
        		result.setMsg("参数出错:数据源主键不可为空");
    			result.setCode("9999");
    			return result;
        	}
        	if("".equals(Util.NulltoString(dataQuickQueryPOJO.getQ_id()))){
        		logger.info("========参数出错:查询脚本ID不可为空========");
        		result.setMsg("参数出错:查询脚本ID不可为空");
    			result.setCode("9999");
    			return result;
        	}
        	DataAutoRun.setT_sql_id(dataQuickQueryPOJO.getQ_id());
        	DataAutoRun.setT_jdbc_id(String.valueOf(dataQuickQueryPOJO.getDataSourceId()));
        	if("".equals(Util.NulltoString(dataQuickQueryPOJO.getSql()))){
        		logger.info("========参数出错:查询脚本不可为空========");
        		result.setMsg("参数出错:查询脚本不可为空");
    			result.setCode("9999");
    			return result;
        	}
        	DataAutoRun.setT_sql(dataQuickQueryPOJO.getSql());
        	DataAutoRun.setT_userid(user.getU_id());
        	DataAutoRun.setT_date(BMUtil.getNowTimeForString());
        	if(dataSourcePOJODao.InsertAutoRun(DataAutoRun)>0){
        		result.setMsg("异步查询数据提交成功");
    			result.setCode("0001");
        	}else{
        		result.setMsg("异步查询数据提交失败");
    			result.setCode("9999");
        	}
        	return result;
    	}catch(Exception e){
    		logger.error(e);
    		logger.info("========异步查询数据提交异常========");
    		result.setMsg("异步查询数据提交异常");
			result.setCode("9999");
			return result;
    	}
	}
    
    @Override
    public List<DataAutoRunPOJO> getQueryAutoList(DataAutoRunPOJO dataAutoRun){
    	return dataSourcePOJODao.getQueryAutoList(dataAutoRun);
    }
    
    public int deleteAutoRun(String t_id){
    	return dataSourcePOJODao.deleteAutoRun(t_id);
    }
}
